


--use PolePosition

-- // checksum failed custom error
--exec sp_addmessage @msgnum = 505050, @severity = 16, @msgtext = N'%s(%s) checksum compare failed', @replace = 'REPLACE', @lang = 'us_english'
GO

-- // not found custom error
--exec sp_addmessage @msgnum = 505060, @severity = 16, @msgtext = N'%s(%s) does not exist', @replace = 'REPLACE', @lang = 'us_english'
GO

-- // unit test custom error
--exec sp_addmessage @msgnum = 505070, @severity = 16, @msgtext = N'%s Test Failed. Expected< %s > Actual< %s > %s', @replace = 'REPLACE', @lang = 'us_english'
GO



--use PolePosition

if exists (select * from dbo.sysobjects where id = object_id(N'v_tb_Reports_Project') and OBJECTPROPERTY(id, N'IsProcedure') = 0)
drop view v_tb_Reports_Project
GO

create view v_tb_Reports_Project as
	/* 	Initial Generated Date:  Friday, April 03, 2009 
		Purpose: Selected record(s) from the table; this view is the source for all procedures' select statements
	*/
	
select
	 [ReportId] as [ReportId]
	,[ReportFileName] as [ReportFileName]
	,[ReportDisplayName] as [ReportDisplayName]
	,[ReportDescription] as [ReportDescription]
	,[ProjectTypeFk] as [ProjectTypeFk]
	,[Active] as [Active]
	-- customization may be required for FriendlyName
	,Left(CONVERT(varchar(255), [ReportFileName]), 255) as [FriendlyName]
from
	tb_Reports_Project
GO





--use PolePosition

if exists (select * from dbo.sysobjects where id = object_id(N'up_ReportsProject_Test') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure up_ReportsProject_Test
GO


create proc up_ReportsProject_Test
	@ReportId int
as
	/* 	Initial Generated Date:  Friday, April 03, 2009 
		Purpose: Test if a record exists, using the primary key
	*/

SET NOCOUNT ON	
	
if exists(
	select [ReportId] 
	from [tb_Reports_Project] source
	where [source].[ReportId] = @ReportId)
begin
	return(1)
end
else
begin
	return(0)
end
GO






--use PolePosition

if exists (select * from dbo.sysobjects where id = object_id(N'up_ReportsProject_Select') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure up_ReportsProject_Select
GO

create proc up_ReportsProject_Select
	 @ReportId int = null 
	,@ReportFileName varchar(255) = null 
	,@ReportDisplayName varchar(255) = null 
	,@ReportDescription varchar(max) = null 
	,@ProjectTypeFk tinyint = null 
	,@Active bit = null 
as
	/* 	Initial Generated Date:  Friday, April 03, 2009 
		Purpose: Selected record(s) based on the passed arguments; no arguments returns all
	*/

SET NOCOUNT ON	

select * from v_tb_Reports_Project
where
		IsNull([v_tb_Reports_Project].[ReportId], -1) = Coalesce(@ReportId, [v_tb_Reports_Project].[ReportId], -1)
	AND	IsNull([v_tb_Reports_Project].[ReportFileName], '') = Coalesce(@ReportFileName, [v_tb_Reports_Project].[ReportFileName], '')
	AND	IsNull([v_tb_Reports_Project].[ReportDisplayName], '') = Coalesce(@ReportDisplayName, [v_tb_Reports_Project].[ReportDisplayName], '')
	AND	IsNull([v_tb_Reports_Project].[ReportDescription], '') = Coalesce(@ReportDescription, [v_tb_Reports_Project].[ReportDescription], '')
	AND	IsNull([v_tb_Reports_Project].[ProjectTypeFk], 0) = Coalesce(@ProjectTypeFk, [v_tb_Reports_Project].[ProjectTypeFk], 0)
	AND	IsNull([v_tb_Reports_Project].[Active], 0) = Coalesce(@Active, [v_tb_Reports_Project].[Active], 0)
GO





--use PolePosition

if exists (select * from dbo.sysobjects where id = object_id(N'up_ReportsProject_Select_Top1') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure up_ReportsProject_Select_Top1
GO

create proc up_ReportsProject_Select_Top1
	 @ReportId int = null 
	,@ReportFileName varchar(255) = null 
	,@ReportDisplayName varchar(255) = null 
	,@ReportDescription varchar(max) = null 
	,@ProjectTypeFk tinyint = null 
	,@Active bit = null 
as
	/* 	Initial Generated Date:  Friday, April 03, 2009 
		Purpose: Selected top 1 record based on the passed arguments; no arguments returns all
	*/

SET NOCOUNT ON	

select TOP 1 * from v_tb_Reports_Project
where
		IsNull([v_tb_Reports_Project].[ReportId], -1) = Coalesce(@ReportId, [v_tb_Reports_Project].[ReportId], -1)
	AND	IsNull([v_tb_Reports_Project].[ReportFileName], '') = Coalesce(@ReportFileName, [v_tb_Reports_Project].[ReportFileName], '')
	AND	IsNull([v_tb_Reports_Project].[ReportDisplayName], '') = Coalesce(@ReportDisplayName, [v_tb_Reports_Project].[ReportDisplayName], '')
	AND	IsNull([v_tb_Reports_Project].[ReportDescription], '') = Coalesce(@ReportDescription, [v_tb_Reports_Project].[ReportDescription], '')
	AND	IsNull([v_tb_Reports_Project].[ProjectTypeFk], 0) = Coalesce(@ProjectTypeFk, [v_tb_Reports_Project].[ProjectTypeFk], 0)
	AND	IsNull([v_tb_Reports_Project].[Active], 0) = Coalesce(@Active, [v_tb_Reports_Project].[Active], 0)
GO




--use PolePosition

if exists (select * from dbo.sysobjects where id = object_id(N'up_ReportsProject_Insert') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure up_ReportsProject_Insert
GO

create proc up_ReportsProject_Insert
	 @ReportId int =null OUTPUT  
	,@ReportFileName varchar(255)  
	,@ReportDisplayName varchar(255)  
	,@ReportDescription varchar(max) = null  
	,@ProjectTypeFk tinyint = null  
	,@Active bit  
as
	/* 	Initial Generated Date:  Friday, April 03, 2009 
		Purpose: Inserts a new record; missing arguments are set as their default (typically null)
	*/

SET NOCOUNT ON	
--int

insert tb_Reports_Project 
	(
	
	 [ReportFileName] 
	,[ReportDisplayName] 
	,[ReportDescription] 
	,[ProjectTypeFk] 
	,[Active] 
	)
	values
	(
	
	 @ReportFileName 
	,@ReportDisplayName 
	,@ReportDescription 
	,@ProjectTypeFk 
	,@Active 
	)
set @ReportId = scope_identity()
select * from v_tb_Reports_Project where ReportId = @ReportId

GO




--use PolePosition

if exists (select * from dbo.sysobjects where id = object_id(N'up_ReportsProject_Update') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure up_ReportsProject_Update
GO


create proc up_ReportsProject_Update
	 @ReportId int  
	,@ReportFileName varchar(255) = null  
	,@ReportDisplayName varchar(255) = null  
	,@ReportDescription varchar(max) = null  
	,@ProjectTypeFk tinyint = null  
	,@Active bit = null  
	,@OriginalReportId int  = null --Original PrimaryKey
	
as
	/* 	Initial Generated Date:  Friday, April 03, 2009 
		Purpose: Updates an existing record, verifying exist; missing arguments persist original record value
		NOTE: this version of the update allows up to write nulls to the table.
	*/

SET NOCOUNT ON	

if @OriginalReportId is null
	set @OriginalReportId = @ReportId

-- test
declare @Result bit
execute @Result = up_ReportsProject_Test @OriginalReportId
if (not @Result = 1)
begin
	Declare @x varchar(100)
	Set @x = Convert(Varchar(100), @ReportId)
	raiserror(505060, 16, 1, 'ReportsProject', @x)
	return
end

-- proceed
update tb_Reports_Project 
set

	 [ReportFileName] = IsNull(@ReportFileName, [source].[ReportFileName])
	,[ReportDisplayName] = IsNull(@ReportDisplayName, [source].[ReportDisplayName])
	,[ReportDescription] =  @ReportDescription
	,[ProjectTypeFk] =  @ProjectTypeFk
	,[Active] = IsNull(@Active, [source].[Active])
	
from tb_Reports_Project source
where
	[source].[ReportId] = @OriginalReportId


select * from v_tb_Reports_Project where ReportId = @ReportId
GO





--use PolePosition

if exists (select * from dbo.sysobjects where id = object_id(N'up_ReportsProject_Delete') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure up_ReportsProject_Delete
GO


create proc up_ReportsProject_Delete
	@ReportId int
as
	/* 	Initial Generated Date:  Friday, April 03, 2009 
		Purpose: Deletes an existing record, verifying exist
	*/

SET NOCOUNT ON	

-- test
declare @Result bit
execute @Result = up_ReportsProject_Test @ReportId
if (not @Result = 1)
begin
	Declare @x varchar(100)
	Set @x = Convert(Varchar(100), @ReportId)
	raiserror(505060, 16, 1, 'ReportsProject', @x)
	return
end

-- proceed
delete from tb_Reports_Project 
where [ReportId] = @ReportId

GO


